USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_5]    Script Date: 11/23/2011 15:15:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_5]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_5]
GO

USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_5]    Script Date: 11/23/2011 15:15:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_5]
AS

-- GESION COMERCIAL AFILIADOS

TRUNCATE TABLE GESTION_MORA.DBO.GMComercialAfil

INSERT INTO DBO.GMComercialAfil
SELECT     
	 GMBASEGESTCOMER.Cuenta_Empresa 
	,GMBASEGESTCOMER.Nombre_Empresa 
	,GMBASEGESTCOMER.Estado_Empresa 
	,GMBASEGESTCOMER.Condicion_Empresa 
	,GMBASEGESTCOMER.Clasificacion_Empresa 
	,GMBASEGESTCOMER.SucOper 
	,GMBASEGESTCOMER.Agencia 
	,GMBASEGESTCOMER.GERENTE 
	,GMBASEGESTCOMER.RC 
	,GMBASEGESTCOMER.RG 
	,GMBASEGESTCOMER.C_MANT 
	,GMBASEGESTCOMER.CI_MANT	
	,GMBASEGESTCOMER.EJECUTIVO_EMPRESAS 
	,GMBASEGESTCOMER.SEGMENTO 
	,GMBASEGESTCOMER.CR 
	,GMBASEGESTCOMER.ESTADO 
	,GMBASEGESTCOMER.CONTRATO 
	,GMBASEGESTCOMER.Cuenta 
	,GMBASEGESTCOMER.Operacion 
	,GMBASEGESTCOMER.Nombre_Deudor 
	,GMBASEGESTCOMER.Cuenta_Aval1 
	,GMBASEGESTCOMER.Nombre_Aval1 
	,GMBASEGESTCOMER.Cuenta_Aval2 
	,GMBASEGESTCOMER.Nombre_Aval2 
	,GMBASEGESTCOMER.MM60 
	,GMBASEGESTCOMER.DP 
	,GMBASEGESTCOMER.DR 
	,GMHISTORICOSALDO.Clasificacion 
	,Vcto_Impago = convert(varchar, (GMBASEGESTCOMER.Vcto_Impago),105)  
	,GMBASEGESTCOMER.Valor_Cuota
FROM		
	GMBASEGESTCOMER (nolock) Left Join GMHISTORICOSALDO (nolock) ON
    	GMBASEGESTCOMER.Operacion = GMHISTORICOSALDO.Operacion AND 
    	GMBASEGESTCOMER.Cuenta    = GMHISTORICOSALDO.Cuenta 
--	GMBASEGESTCOMER (nolock)
--	,GMHISTORICOSALDO (nolock)
WHERE
--	GMBASEGESTCOMER.Operacion *= GMHISTORICOSALDO.Operacion AND 
--	GMBASEGESTCOMER.Cuenta *= GMHISTORICOSALDO.Cuenta AND
	GMBASEGESTCOMER.Condicion_Empresa = 'EMPRESA ADHERIDA' AND 
	GMBASEGESTCOMER.Estado_Empresa = 'AFILIADO' AND 
	GMHISTORICOSALDO.Clasificacion = 'ME - Analizada' AND 
	GMBASEGESTCOMER.Valor_Cuota >= 1000 AND
	GMHISTORICOSALDO.Fecha = cast(convert(varchar ,getdate(),112) as datetime)

GROUP BY	
	GMBASEGESTCOMER.Cuenta_Empresa, GMBASEGESTCOMER.Nombre_Empresa,  GMBASEGESTCOMER.Estado_Empresa, GMBASEGESTCOMER.Condicion_Empresa, 
	GMBASEGESTCOMER.Clasificacion_Empresa, GMBASEGESTCOMER.SucOper, GMBASEGESTCOMER.Agencia, GMBASEGESTCOMER.GERENTE, GMBASEGESTCOMER.RC, 
	GMBASEGESTCOMER.RG, GMBASEGESTCOMER.[C_MANT], GMBASEGESTCOMER.[CI_MANT], GMBASEGESTCOMER.EJECUTIVO_EMPRESAS, GMBASEGESTCOMER.SEGMENTO, 
	GMBASEGESTCOMER.CR, GMBASEGESTCOMER.ESTADO, GMBASEGESTCOMER.CONTRATO, GMBASEGESTCOMER.Cuenta, 	GMBASEGESTCOMER.Operacion, GMBASEGESTCOMER.Nombre_Deudor, 
	GMBASEGESTCOMER.Cuenta_Aval1, GMBASEGESTCOMER.Nombre_Aval1, GMBASEGESTCOMER.Cuenta_Aval2, GMBASEGESTCOMER.Nombre_Aval2, GMBASEGESTCOMER.MM60, 
	GMBASEGESTCOMER.DP, GMBASEGESTCOMER.DR, GMHISTORICOSALDO.Clasificacion, GMBASEGESTCOMER.Vcto_Impago, GMBASEGESTCOMER.Valor_Cuota

-- Detalle Afiliados
TRUNCATE TABLE DBO.GMDETAFIL

INSERT INTO DBO.GMDETAFIL
SELECT		
	GMComercialAfil.Cuenta_Empresa 
	,GMComercialAfil.Nombre_Empresa 
	,GMComercialAfil.Cuenta 
	,GMComercialAfil.Operacion 
        ,GMComercialAfil.Nombre_Deudor
	,Vcto_Impago = GMMora.Z073B3Fpag 
	,Valor_Cuota = CONVERT(NUMERIC, GMMora.Z073B3CNo) 
FROM         
	GMComercialAfil(nolock) Left Join	GMMora (nolock) ON
	 	GMComercialAfil.Cuenta    = GESTION_MORA.dbo.GMMora.Z073B3Cta AND 
	  GMComercialAfil.Operacion = GESTION_MORA.dbo.GMMora.Z073B3Oper
--	GMComercialAfil(nolock) ,
--	GMMora (nolock)
--WHERE
--	GMComercialAfil.Cuenta    *= GESTION_MORA.dbo.GMMora.Z073B3Cta AND 
--	GMComercialAfil.Operacion *= GESTION_MORA.dbo.GMMora.Z073B3Oper
	
GO

